package cn.ls.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * ConnectionUtil
 * @author senda
 *
 */
public class ConnectionUtil {

	/**
	 * 查询道路十二点的早高峰时段的AADT
	 * @author senda
	 * @return
	 */
	public static List<HashMap<String, Object>> findProfits(){
		//首先导入SQL驱动
		String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
		Connection con=null;
		Statement stmt = null;
		ResultSet rs = null;
		//下面一行中数据库名称是你自己在SQL server中创建的
		String dbURL="jdbc:sqlserver://localhost:1433;DatabaseName=California";
		String userName="sa";
		String userPwd="920806";
		try{
			Class.forName(driverName);	
			System.out.println("加载驱动成功！");
		    con=DriverManager.getConnection(dbURL,userName,userPwd);
		    if(con!=null){
		    	System.out.println("连接数据库成功！");
		    }
		    stmt = con.createStatement();
		    rs = stmt.executeQuery("SELECT TOP 12 [Id]"+
									      ",[Ahead_pk_h]"+
									"FROM [California].[dbo].[AADT2010b$]");
		    
		    HashMap<String,Object> map = null;
		    List<HashMap<String,Object>> maps = new ArrayList<HashMap<String, Object>>();
		    
		    while(rs.next()){
		    	map = new HashMap<String,Object>();
		    	map.put("point", rs.getFloat("ID"));
		    	map.put("AADT", rs.getFloat("Ahead_pk_h"));
		    	maps.add(map);
		    }
		    //关闭流：后打开的先关闭
		    rs.close();
		    stmt.close();
		    con.close();
		    return maps;
		}catch(Exception e){
			e.printStackTrace();
			return null;			
		}

		
	}
	public static void main(String[] args){
		List<HashMap<String, Object>> maps = findProfits();
		for(HashMap<String, Object> map:maps){
			System.out.println(map.get("point")+"===="+map.get("AADT"));
		}
		
	}
}
